package com.xyc.petmallsystem.repository;

import com.xyc.petmallsystem.pojo.User;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

/**
 * @author Joel
 * @date 2021/11/17
 *
 *
 * 不支持 executeQuery
 */
public interface UserRepository extends JpaRepository<User, Integer> {

    @Modifying
    @Transactional
    @Query(value = "alter table user auto_increment = :id ", nativeQuery = true)
    void setAutoIncrement(Integer id);


    @Query(value = "select * from user where name = :name and pwd = :pwd", nativeQuery = true)
    User login(String name, String pwd);

    /*@Query(value = "DROP TABLE `user`;\n" +
            "\n" +
            "CREATE TABLE IF NOT EXISTS `user`(\n" +
            "\tid int(10) PRIMARY KEY AUTO_INCREMENT,\n" +
            "    name varchar(30),\n" +
            "    pwd varchar(30)\n" +
            ")\n" +
            "\n" +
            "INSERT INTO `user` ( `name`, `pwd`) VALUES( '张三', 'zs');\n" +
            "INSERT INTO `user` ( `name`, `pwd`) VALUES('李四', 'ls');\n" +
            "INSERT INTO `user` ( `name`, `pwd`) VALUES('赵五', 'zw');\n" +
            "INSERT INTO `user` ( `name`, `pwd`) VALUES('test','test');\n" +
            "INSERT INTO `user` ( `name`, `pwd`) VALUES('a1','a1');\n" +
            "INSERT INTO `user` ( `name`, `pwd`) VALUES('a2','a2');\n" +
            "INSERT INTO `user` ( `name`, `pwd`) VALUES('a3','a3');",nativeQuery = true)
    void init();*/

    /*
    根据name获取id
     */
    @Query(value = "select id from user where name = :name", nativeQuery = true)
    Integer getUserIdByName(String name);

    /*
    查询用户的数量
     */
    @Query(value = "select count(*) from user", nativeQuery = true)
    Integer getUserCount();


    /*
    判断name是否存在
     */
    @Query(value = "select * from user where name = :name", nativeQuery = true)
    User findByName(String name);
}
